/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package database;
import java.math.BigInteger;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
/**
 *
 * @author Erdiansyah
 */
public class database {
    private Connection connect = null;
    private Statement statement = null;
    private ResultSet resultSet = null;

    private static String dbname = "wedchat";
    private static String mysqluser = "root";
    private static String mysqlpass = "";

    public database(){
    }

    public void createConnection() throws ClassNotFoundException, SQLException{
        Class.forName("com.mysql.jdbc.Driver");
        this.connect = DriverManager.getConnection("jdbc:mysql://localhost/"+this.dbname+"?user="+this.mysqluser+"&password="+this.mysqlpass);
        statement = connect.createStatement();
    }

    public static Connection dbConn(String dbname, String mysqluser,String mysqlpass){
        Connection connect;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connect = DriverManager.getConnection("jdbc:mysql://localhost/" + dbname + "?user=" + mysqluser + "&password=" + mysqlpass);
            return connect;
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(database.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(database.class.getName()).log(Level.SEVERE, null, ex);
        }

        return null;
    }

    public Connection getConnect() {
        return connect;
    }
    
    public void closeConnection() throws SQLException{
        this.connect.close();
    }

    public void select(String query) throws SQLException{
        this.resultSet = this.statement.executeQuery(query);
    }

    public void update(String query) throws SQLException{
        statement.executeUpdate(query);
    }

    public ResultSet getResultSet(){
        return this.resultSet;
    }

    public static String convert_md5(String input) throws NoSuchAlgorithmException {
        String output;

        byte[] passbyte = input.getBytes();

        MessageDigest m = MessageDigest.getInstance("MD5");

        m.update(passbyte, 0, input.length());
        output = new BigInteger(1, m.digest()).toString(16);
        return output;
    }

    public void addUser(String name,String username,String password,String email) throws SQLException, NoSuchAlgorithmException{
        statement.executeUpdate("INSERT INTO `userdb` (name, username, password, email) VALUES ('"+name+"','"+username+"','"+convert_md5(password)+"','"+email+"')");
    }

    public void removeUser(String username) throws SQLException{
        statement.executeUpdate("DELETE FROM `userdb` WHERE `username`="+username);
    }

    public void addFriend(String username, String fusername) throws SQLException{
        statement.executeUpdate("INSERT INTO `frienddb` VALUES ('"+username+"','"+fusername+"')");
    }

    public void removeFriend(String username, String fusername) throws SQLException{
        statement.executeUpdate("DELETE FROM `frienddb` WHERE `user`="+username+" AND `userfriend`="+fusername);
    }

    public static boolean isOnlineUser(String username, Connection conn) throws SQLException{
        String query = "SELECT `statuslogin` FROM `userdb` WHERE `username`='"+username+"'";
        Statement st = conn.createStatement();
        ResultSet rs = st.executeQuery(query);
        rs.next();
        if(rs.getBoolean("statuslogin") == false){
            return false;
        }else
            return true;
    }

    public static boolean isExistUsername(String username,Connection conn) throws SQLException{
        String query = "SELECT `username` FROM `userdb` WHERE `username`='"+username+"'";
        Statement st = conn.createStatement();
        ResultSet rs = st.executeQuery(query);
        if(rs.next()== false){
            return false;
        }else
            return true;
    }

    public void setRoomCount(String username,int roomcount) throws SQLException{
        update("UPDATE `userdb` SET `roomcount`="+roomcount+" WHERE `username`="+username);
    }

    public void setLogin(String username) throws SQLException{
        update("UPDATE `userdb` SET `statuslogin`= 1 WHERE `username`= '"+username+"'");
    }

    public void setLogout(String username) throws SQLException{
        update("UPDATE `userdb` SET `statuslogin`= 0 WHERE `username`= '"+username+"'");
    }


    public void setBanned(String username) throws SQLException{
        update("UPDATE `userdb` SET `statusbanned`= 1 WHERE `username`="+username);
    }

    public void setUnBanned(String username) throws SQLException{
        update("UPDATE `userdb` SET `statusbanned`= 0 WHERE `username`="+username);
    }

    public void updateTimeLogin(String username) throws SQLException{
        update("UPDATE `userdb` SET `timelogin`= Now() WHERE `username`="+username);
    }

    public void setRole(String username,String role) throws SQLException{
        update("UPDATE `userdb` SET `role`="+role+" WHERE `username`="+username);
    }

    public void addCategory(String catname) throws SQLException{
        String code;
        if(catname.length() >5)
            code = (String)catname.substring(0, 4);
        else
            code = catname;
        update("INSERT INTO `categorydb` (categoryname, categorycode) VALUES ('"+catname+"','"+code+"')");
    }

    public void updateCategoryName(String catname,String updvalue) throws SQLException{
        String code;
        if(catname.length() >5)
            code = (String)catname.substring(0, 4);
        else
            code = catname;
        update("UPDATE `categorydb` SET `categoryname`="+updvalue+", `categorycode`="+code+" WHERE `categoryname`="+catname);
    }

    public void updateSubCategoryName(String subcatname, String updvalue) throws SQLException{
         String code;
        if(subcatname.length() >5)
            code = (String)subcatname.substring(0, 4);
        else
            code = subcatname;
        update("UPDATE `subcategorydb` SET `subcategoryname`="+updvalue+", `categorycode`="+code+" WHERE `subcategoryname`="+subcatname);
    }

    public void removeCategory(String catname) throws SQLException{
        update("DELETE FROM `categorydb` WHERE `categoryname`="+catname);
    }

    public void removeCategory(int catid) throws SQLException{
        update("DELETE FROM `categorydb` WHERE `categoryid`="+catid);
    }

    public void addSubCategoryToCategory(String subcatname,String catname) throws SQLException{
        String code;
        if(subcatname.length() >5)
            code = (String)subcatname.substring(0, 5);
        else
            code = subcatname;
        select("SELECT categoryid FROM `categorydb` WHERE `categoryname`="+catname);
        int categoryid = resultSet.getInt("categoryid");
        update("INSERT INTO `subcategorydb` (subcategoryname, subcategorycode, categoryid) VALUES ('"+subcatname+"','"+code+"',"+categoryid+")");
    }

    public void removeSubCategory(String subcatname) throws SQLException{
        update("DELETE FROM `subcategorydb` WHERE `subcategoryname`="+subcatname);
    }

    public void retrieveCategoryList() throws SQLException{
        select("SELECT * FROM `categorydb`");
    }

    public int getParentID(int id) throws SQLException{
        ResultSet newresultSet = this.statement.executeQuery("SELECT * FROM `categorydb` WHERE categoryid="+id);
        newresultSet.next();
        return newresultSet.getInt("parentid");
    }

    public String getCategoryName(int id) throws SQLException{
        ResultSet newresultSet = this.statement.executeQuery("SELECT * FROM `categorydb` WHERE categoryid="+id);
        newresultSet.next();
        return newresultSet.getString("categoryname");
    }

    public String getCategoryCode(int id) throws SQLException{
        ResultSet newresultSet = this.statement.executeQuery("SELECT * FROM `categorydb` WHERE categoryid="+id);
        newresultSet.next();
        return newresultSet.getString("categorycode");
    }

    public String getUserName(int id) throws SQLException{
        ResultSet newresultSet = this.statement.executeQuery("SELECT * FROM `userdb` WHERE id="+id);
        newresultSet.next();
        return newresultSet.getString("username");
    }

    public int getRoomCount(int id) throws SQLException{
        ResultSet newresultSet = this.statement.executeQuery("SELECT * FROM `userdb` WHERE id="+id);
        newresultSet.next();
        return newresultSet.getInt("roomcount");
    }

    public String getRole(int id) throws SQLException{
        ResultSet newresultSet = this.statement.executeQuery("SELECT * FROM `userdb` WHERE id="+id);
        newresultSet.next();
        return newresultSet.getString("role");
    }

    public int getStatusBanned(int id) throws SQLException{
        ResultSet newresultSet = this.statement.executeQuery("SELECT * FROM `userdb` WHERE id="+id);
        newresultSet.next();
        return newresultSet.getInt("statusbanned");
    }

    public int getCategoryModerator(int id) throws SQLException{
        ResultSet newresultSet = this.statement.executeQuery("SELECT * FROM `userdb` WHERE id="+id);
        newresultSet.next();
        return newresultSet.getInt("category");
    }

    public boolean isCategoryDescendant(int id, int parentid) throws SQLException{
        if(id == parentid){
            return true;
        }
        if(id == 0){
            return false;
        }
        ResultSet newresultSet = this.statement.executeQuery("SELECT * FROM `categorydb` WHERE categoryid="+id);
        newresultSet.next();
        return isCategoryDescendant(newresultSet.getInt("parentid"), parentid);
    }

    public ArrayList<Integer> getChild(int id) throws SQLException{
        List<Integer> outputList = new ArrayList<Integer>();
        select("SELECT * FROM categorydb");
        while(resultSet.next()){
            if(resultSet.getInt("parentid") == id){
                outputList.add(resultSet.getInt("categoryid"));
            }
        }
        return (ArrayList<Integer>) outputList;
    }

    /*public ArrayList<User> retUser() throws SQLException{
        select("SELECT * FROM `userdb`");
        while(resultSet.next()){
            //User user = new User(resultSet.getString("name"), resultSet.getString("usernmae"), resultSet.getString("role"), );
        }
        return null;
    }*/

}
